﻿using System;
using System.Text;
using System.Collections.Generic;
using System.Collections;
using System.Reflection;
using Common.SQL;
using System.Data;

namespace MRS.DBL.DAL
{
    public partial class UsersPageDAL : SQLQueryPageT<Model.User>
    {
        static log4net.ILog logger = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

        /// <summary>
        /// 转换数据库结果集为Model列表
        /// </summary>
        /// <param name="dataReader"></param>
        /// <returns></returns>
        protected override Model.User[] FillData(IDataReader dataReader)
        {
            List<Model.User> arrData = new List<Model.User>();

            while (dataReader.Read())
            {
                MRS.DBL.Model.User model = new MRS.DBL.Model.User();

                if (dataReader["Id"] != null && dataReader["Id"].ToString() != "")
                {
                    model.Id = int.Parse(dataReader["Id"].ToString());
                }
                if (dataReader["UserName"] != null && dataReader["UserName"].ToString() != "")
                {
                    model.UserName = dataReader["UserName"].ToString();
                }
                if (dataReader["Password"] != null && dataReader["Password"].ToString() != "")
                {
                    model.Password = dataReader["Password"].ToString();
                }
                if (dataReader["RealName"] != null && dataReader["RealName"].ToString() != "")
                {
                    model.RealName = dataReader["RealName"].ToString();
                }
                if (dataReader["Sex"] != null && dataReader["Sex"].ToString() != "")
                {
                    model.Sex = dataReader["Sex"].ToString();
                }
                if (dataReader["Birthday"] != null && dataReader["Birthday"].ToString() != "")
                {
                    model.Birthday = DateTime.Parse(dataReader["Birthday"].ToString());
                }
                if (dataReader["RoleId"] != null && dataReader["RoleId"].ToString() != "")
                {
                    model.RoleId = int.Parse(dataReader["RoleId"].ToString());
                }
                if (dataReader["Status"] != null && dataReader["Status"].ToString() != "")
                {
                    model.Status = dataReader["Status"].ToString();
                }
                if (dataReader["UnitId"] != null && dataReader["UnitId"].ToString() != "")
                {
                    model.UnitId = int.Parse(dataReader["UnitId"].ToString());
                }
                if (dataReader["Phone"] != null && dataReader["Phone"].ToString() != "")
                {
                    model.Phone = dataReader["Phone"].ToString();
                }
                if (dataReader["Email"] != null && dataReader["Email"].ToString() != "")
                {
                    model.Email = dataReader["Email"].ToString();
                }
                if (dataReader["GroupId"] != null && dataReader["GroupId"].ToString() != "")
                {
                    model.GroupId = int.Parse(dataReader["GroupId"].ToString());
                }
                if (dataReader["CreateUserId"] != null && dataReader["CreateUserId"].ToString() != "")
                {
                    model.CreateUserId = int.Parse(dataReader["CreateUserId"].ToString());
                }
                if (dataReader["Online"] != null && dataReader["Online"].ToString() != "")
                {
                    model.Online = dataReader["Online"].ToString();
                }
                if (dataReader["CreateTime"] != null && dataReader["CreateTime"].ToString() != "")
                {
                    model.CreateTime = DateTime.Parse(dataReader["CreateTime"].ToString());
                }
                if (dataReader["LastLoginTime"] != null && dataReader["LastLoginTime"].ToString() != "")
                {
                    model.LastLoginTime = DateTime.Parse(dataReader["LastLoginTime"].ToString());
                }
                if (dataReader["Ext1"] != null && dataReader["Ext1"].ToString() != "")
                {
                    model.Ext1 = dataReader["Ext1"].ToString();
                }
                if (dataReader["Ext2"] != null && dataReader["Ext2"].ToString() != "")
                {
                    model.Ext2 = dataReader["Ext2"].ToString();
                }
                if (dataReader["Ext3"] != null && dataReader["Ext3"].ToString() != "")
                {
                    model.Ext3 = dataReader["Ext3"].ToString();
                }

                arrData.Add(model);
            }

            dataReader.Close();

            return arrData.ToArray();
        }

        protected void SetSQL(MRS.DBL.Model.User model, MRS.DBL.Model.User model2, bool asc)
        {
            string WhereCondition = this.GetQuerySqlWhereConditionByUserModel(model, model2);
            this.CountSQL = "select count(1) from users " + WhereCondition;
            this.SQL = "select users.Id, users.UserName, users.Password, users.RealName, users.Sex, users.Birthday, users.RoleId, users.Status, users.UnitId, users.Phone, users.Email, users.GroupId, users.CreateUserId, users.Online, users.CreateTime, users.LastLoginTime, users.Ext1, users.Ext2, users.Ext3  " + "  from users " + WhereCondition;
            if (asc)
                this.SQL += "order by LastLoginTime asc";
            else
                this.SQL += "order by LastLoginTime desc";

        }

        // <summary>
        /// 功能：根据两个对象拼接出查询的SQL语句
        /// 说明：如果对象1中的日期时间有值，条件为大于该时间；如果对象2中的日期时间有值，条件为小于该时间
        /// </summary>
        /// <param name="userBasic1"></param>
        /// <param name="userBasic2"></param>
        /// <returns></returns>
        private string GetQuerySqlWhereConditionByUserModel(MRS.DBL.Model.User model, MRS.DBL.Model.User model2)
        {
            StringBuilder sqlBuilder = new StringBuilder();

            if (model.Id > 0)
                sqlBuilder.AppendFormat((sqlBuilder.Length > 0 ? " and " : "") + " users.Id={0} ", model.Id);
            if (!string.IsNullOrEmpty(model.UserName))
                sqlBuilder.AppendFormat((sqlBuilder.Length > 0 ? " and " : "") + " users.UserName like '%{0}%' ", Common.SQL.SQLHelper.GetLikeCondition(model.UserName));
            if (!string.IsNullOrEmpty(model.Password))
                sqlBuilder.AppendFormat((sqlBuilder.Length > 0 ? " and " : "") + " users.Password like '%{0}%' ", Common.SQL.SQLHelper.GetLikeCondition(model.Password));
            if (!string.IsNullOrEmpty(model.RealName))
                sqlBuilder.AppendFormat((sqlBuilder.Length > 0 ? " and " : "") + " users.RealName like '%{0}%' ", Common.SQL.SQLHelper.GetLikeCondition(model.RealName));
            if (!string.IsNullOrEmpty(model.Sex))
                sqlBuilder.AppendFormat((sqlBuilder.Length > 0 ? " and " : "") + " users.Sex like '%{0}%' ", Common.SQL.SQLHelper.GetLikeCondition(model.Sex));
            if (model.Birthday > DateTime.MinValue)
                sqlBuilder.AppendFormat((sqlBuilder.Length > 0 ? " and " : "") + " users.Birthday >= '{0}' ", model.Birthday);
            if (model.RoleId > 0)
                sqlBuilder.AppendFormat((sqlBuilder.Length > 0 ? " and " : "") + " users.RoleId={0} ", model.RoleId);
            if (!string.IsNullOrEmpty(model.Status))
                sqlBuilder.AppendFormat((sqlBuilder.Length > 0 ? " and " : "") + " users.Status like '%{0}%' ", Common.SQL.SQLHelper.GetLikeCondition(model.Status));
            if (model.UnitId > 0)
                sqlBuilder.AppendFormat((sqlBuilder.Length > 0 ? " and " : "") + " users.UnitId={0} ", model.UnitId);
            if (!string.IsNullOrEmpty(model.Phone))
                sqlBuilder.AppendFormat((sqlBuilder.Length > 0 ? " and " : "") + " users.Phone like '%{0}%' ", Common.SQL.SQLHelper.GetLikeCondition(model.Phone));
            if (!string.IsNullOrEmpty(model.Email))
                sqlBuilder.AppendFormat((sqlBuilder.Length > 0 ? " and " : "") + " users.Email like '%{0}%' ", Common.SQL.SQLHelper.GetLikeCondition(model.Email));
            if (model.GroupId > 0)
                sqlBuilder.AppendFormat((sqlBuilder.Length > 0 ? " and " : "") + " users.GroupId={0} ", model.GroupId);
            if (model.CreateUserId > 0)
                sqlBuilder.AppendFormat((sqlBuilder.Length > 0 ? " and " : "") + " users.CreateUserId={0} ", model.CreateUserId);
            if (!string.IsNullOrEmpty(model.Online))
                sqlBuilder.AppendFormat((sqlBuilder.Length > 0 ? " and " : "") + " users.Online like '%{0}%' ", Common.SQL.SQLHelper.GetLikeCondition(model.Online));
            if (model.CreateTime > DateTime.MinValue)
                sqlBuilder.AppendFormat((sqlBuilder.Length > 0 ? " and " : "") + " users.CreateTime >= '{0}' ", model.CreateTime);
            if (model.LastLoginTime > DateTime.MinValue)
                sqlBuilder.AppendFormat((sqlBuilder.Length > 0 ? " and " : "") + " users.LastLoginTime >= '{0}' ", model.LastLoginTime);
            if (!string.IsNullOrEmpty(model.Ext1))
                sqlBuilder.AppendFormat((sqlBuilder.Length > 0 ? " and " : "") + " users.Ext1 like '%{0}%' ", Common.SQL.SQLHelper.GetLikeCondition(model.Ext1));
            if (!string.IsNullOrEmpty(model.Ext2))
                sqlBuilder.AppendFormat((sqlBuilder.Length > 0 ? " and " : "") + " users.Ext2 like '%{0}%' ", Common.SQL.SQLHelper.GetLikeCondition(model.Ext2));
            if (!string.IsNullOrEmpty(model.Ext3))
                sqlBuilder.AppendFormat((sqlBuilder.Length > 0 ? " and " : "") + " users.Ext3 like '%{0}%' ", Common.SQL.SQLHelper.GetLikeCondition(model.Ext3));

            //第二个model
            if (model2 != null && model2.Birthday > DateTime.MinValue)
                sqlBuilder.AppendFormat((sqlBuilder.Length > 0 ? " and " : "") + " users.Birthday <= '{0}' ", model2.Birthday);
            if (model2 != null && model2.CreateTime > DateTime.MinValue)
                sqlBuilder.AppendFormat((sqlBuilder.Length > 0 ? " and " : "") + " users.CreateTime <= '{0}' ", model2.CreateTime);
            if (model2 != null && model2.LastLoginTime > DateTime.MinValue)
                sqlBuilder.AppendFormat((sqlBuilder.Length > 0 ? " and " : "") + " users.LastLoginTime <= '{0}' ", model2.LastLoginTime);



            if (sqlBuilder.Length > 0)
                return " where " + sqlBuilder.ToString();
            else return null;
        }
    }
}
